package com.studorm.action;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.apache.struts2.ServletActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.studorm.Connection_mysql;

/**
 * @author Waiting_hao
 *
 */
public class DormAction extends ActionSupport {
	private List<String> options = new ArrayList<String>();
	public List<String> getOptions() {
		return options;
	}

	public void setOptions(List<String> options) {
		this.options = options;
	}

	private static final long serialVersionUID = 2811028652992276554L;
	//http://www.cnblogs.com/codeplus/archive/2011/07/18/2109316.html
	public String loadDormNuByName() throws Exception{// 通过宿舍楼名获得宿舍号
		//读取请求的参数 
		HttpServletRequest request = ServletActionContext.getRequest(); 
		String dormBdName = request.getParameter("dornBdName"); // 得到宿舍楼名

		Connection conn = Connection_mysql.getConnection();
		String sql = "select distinct dormNu from dorminf where dormBdName=?";//得到宿舍号
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, dormBdName);
		ResultSet res = ps.executeQuery();
		while(res.next()){
			options.add(res.getString("dormNu"));
		}
		res.close();
		ps.close();
		conn.close();
		
		return SUCCESS;
	}
	
	public String loadDormBdName() throws Exception{// 获得宿舍楼名
		Connection conn = Connection_mysql.getConnection();
		String sql = "select distinct dormBdName from dorminf";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet res = ps.executeQuery();
		while(res.next()){
			options.add(res.getString("dormBdName"));
		}
		res.close();
		ps.close();
		conn.close();
		
		return SUCCESS;
	}
	
	public String loadUtilYear() throws Exception{// 获得水电费年份
		Connection conn = Connection_mysql.getConnection();
		String sql = "select distinct Year(date) as y from utilities";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet res = ps.executeQuery();
		while(res.next()){
			options.add(res.getString("y"));
		}
		res.close();
		ps.close();
		conn.close();
		
		return SUCCESS;
	}
}
